﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace BO
{
    /// <summary>
    /// Summary description for SlideShowBO
    /// </summary>
    public class SlideShowBO : DBConnectionBO
    {
        public SlideShowBO()
            : base()
        {
        }

        /// <summary>
        /// Get list slideshow
        /// </summary>
        /// <returns></returns>
        public List<SlideShow> GetListSlideShow()
        {
            var listSlideshow = new List<SlideShow>();
            const string query = "SELECT TOP 100 * FROM [PrivateBlog].[dbo].[SlideShows]";
            var sqlDataAdapter = new SqlDataAdapter(query, connection);
            var dataTable = new DataTable();
            sqlDataAdapter.Fill(dataTable); // fill data to Datatable
            this.CloseConnection(); // Close connection to database
            for (var i = 0; i < dataTable.Rows.Count; i++)
            {
                var slideshow = new SlideShow
                    {
                        Id = (int)dataTable.Rows[i][0],
                        Name = (string)dataTable.Rows[i][1],
                        Link = (string)dataTable.Rows[i][2],
                        ImagePanorama = (string)dataTable.Rows[i][3],
                        Priority = (int)dataTable.Rows[i][4],
                        LastDateUpdated = (DateTime)dataTable.Rows[i][5],
                        LastUpdatedBy = (string)dataTable.Rows[i][6],
                        Status = Convert.ToInt32(dataTable.Rows[i][7])
                    };
                listSlideshow.Add(slideshow);
            }
            return listSlideshow;
        }

        /// <summary>
        /// Add new a slide show
        /// </summary>
        /// <param name="slideShow"></param>
        /// <returns></returns>
        public int AddSlideShow(SlideShow slideShow)
        {
            var query = "INSERT INTO [PrivateBlog].[dbo].[SlideShows]([Name],[Link],[ImagePanorama],[Priority],[LastDateUpdated],[LastUpdatedBy],[Status]) VALUES(N'"
                        + slideShow.Name + "', N'"
                        + slideShow.Link + "', '"
                        + slideShow.ImagePanorama + "', "
                        + slideShow.Priority
                        + "GETDATE(), N'"
                        + slideShow.LastUpdatedBy + "', "
                        + slideShow.Status;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Edit slide show
        /// </summary>
        /// <param name="slideShow"></param>
        /// <returns></returns>
        public int EditSlideShow(SlideShow slideShow)
        {
            var query = "UPDATE [PrivateBlog].[dbo].[SlideShows]" +
                        "SET NAME = N'" + slideShow.Name + "', " +
                        "Link = '" + slideShow.Link + "', " +
                        "ImagePanorama = '" + slideShow.ImagePanorama + "', " +
                        "Priority = " + slideShow.Priority + ", " +
                        "LastDateUpdated = GETDATE()" +
                        "LastUpdatedBy = " + slideShow.LastUpdatedBy +
                        "Status = " + slideShow.Status +
                        "WHERE Id = " + slideShow.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

        /// <summary>
        /// Delete slide show
        /// </summary>
        /// <param name="slideShow"></param>
        /// <returns></returns>
        public int DeleteSlideShow(SlideShow slideShow)
        {
            var query = "DELETE FROM [PrivateBlog].[dbo].[SlideShows] " +
                        " WHERE Id = " + slideShow.Id;
            var sqlCommand = new SqlCommand(query, connection);
            try
            {
                var status = sqlCommand.ExecuteNonQuery();
                CloseConnection(); // close connection
                return status;
            }
            catch (Exception)
            {
                CloseConnection();
                return 0;
                throw;
            }
        }

    }
}